INSERT INTO FOTO_PRODUCTO
 SELECT PRODUCTO.ID, NULL, NULL FROM PRODUCTO


/******************/

ALTER trigger RegistrarPecosa
   on [dbo].[DETALLE_PECOSA]
   for Insert  
   as
	   declare
	   @ProductoId int,
	   @Fecha date
	   set @ProductoId = (SELECT IdProducto  FROM INSERTED )
	   set @Fecha = (SELECT CONVERT(date, Fecha) FROM INSERTED DP INNER JOIN PECOSA P ON DP.IdPecosa = P.ID)
	   IF ( @ProductoId  = (SELECT IdProducto  FROM KARDEX where IdProducto  = @ProductoId)and 
			@Fecha = (SELECT CONVERT(date, Fecha) FROM KARDEX where IdProducto = @ProductoId and IdDetPecosa is not null) and 
			(SELECT IdDetPecosa FROM KARDEX where IdProducto = @ProductoId and IdDetPecosa is not null) is not null )
		begin
			UPDATE KARDEX 
			SET 
			CantSalida = CantSalida + (SELECT Cantidad FROM INSERTED), 
			NewCantidad = NewCantidad - (SELECT Cantidad FROM INSERTED)
		end
	   
	   ELSE
	   begin
	   INSERT INTO KARDEX( IdProducto, IdDetGuiaEntrada,IdDetPecosa, 
						Documento, Fecha, Costo, 
						CantEntrada,CantSalida, NewCantidad)
			   SELECT pecosa.IdProducto, NULL, pecosa.ID, 
			   PC.NroDoc, GETDATE(), pecosa.ValorUnitario,
			   0, pecosa.Cantidad, P.Stock - pecosa.Cantidad
			   FROM PRODUCTO P INNER JOIN INSERTED pecosa  
			   ON pecosa.IdProducto = P.ID
			   INNER JOIN PECOSA PC on pecosa.IdPecosa = PC.ID
	   end

	   Update P set P.STOCK = P.STOCK - D.Cantidad
			from PRODUCTO as P inner join
			INSERTED as D on D.IdProducto = P.Id
/******************/


/******************/
ALTER trigger [dbo].[ActualizarPecosa]
   on [dbo].[DETALLE_PECOSA]
   for Update
   as
   Update P set P.Stock =P.Stock + D.Cantidad
   from PRODUCTO as P inner join
   DELETED as D on D.IdProducto = P.Id
   
   DELETE FROM KARDEX
   where IdDetPecosa = (SELECT DP.ID FROM DELETED DP)
   and IdProducto = (SELECT DP.IdProducto FROM DELETED DP)
   and CantSalida = (SELECT DP.Cantidad FROM DELETED DP)
   and Fecha = (SELECT P.Fecha FROM DELETED DP
   INNER JOIN PECOSA P ON DP.IdPecosa = P.ID)
     
   INSERT INTO KARDEX( IdProducto, IdDetGuiaEntrada,IdDetPecosa, 
   Fecha, Costo, 
   CantEntrada,CantSalida, NewCantidad)
   
   SELECT  pecosa.IdProducto, NULL, pecosa.ID,
   GETDATE(), pecosa.ValorUnitario,
   0 , pecosa.Cantidad, P.Stock - pecosa.Cantidad
   FROM PRODUCTO P INNER JOIN INSERTED pecosa  
   ON pecosa.IdProducto = P.ID
   
   Update P set P.Stock = P.Stock - I.Cantidad
   from PRODUCTO as P inner join
   INSERTED as I on I.IdProducto = P.Id
/******************/


/******************/
ALTER trigger [dbo].[EliminarPecosa]
   on [dbo].[DETALLE_PECOSA]
   for Delete
   as
   Update P set P.Stock = P.Stock + D.Cantidad
   from PRODUCTO as P inner join
   DELETED as D on D.IdProducto = P.Id
   
   DELETE FROM KARDEX
   where IdDetPecosa = (SELECT Dp.ID FROM DELETED Dp)
   and IdProducto = (SELECT Dp.IdProducto FROM DELETED Dp)
   and CantSalida = (SELECT Dp.Cantidad FROM DELETED Dp)
   and Fecha = (SELECT P.FechaSalida FROM DELETED Dp 
   INNER JOIN PECOSA P ON Dp.IdPecosa = P.ID)

/******************/


/******************/
ALTER trigger [dbo].[RegistrarGuiaEntrada]
   on [dbo].[DETALLE_GUIA_ENTRADA]
   for Insert
   as
		declare
		@ProductoId int,
		@Fecha date
		set @ProductoId = (SELECT IdProducto  FROM INSERTED )
	    set @Fecha = (SELECT CONVERT(date, Fecha) FROM INSERTED DG INNER JOIN GUIA_ENTRADA G ON DG.IdGuiaEntrada = G.ID)
	    
	    IF ( @ProductoId  = (SELECT IdProducto  FROM KARDEX where IdProducto  = @ProductoId)and 
			@Fecha = (SELECT CONVERT(date, Fecha) FROM KARDEX where IdProducto = @ProductoId and IdDetGuiaEntrada is not null) and 
			(SELECT IdDetGuiaEntrada FROM KARDEX where IdProducto = @ProductoId and IdDetGuiaEntrada is not null) is not null )
		begin
			UPDATE KARDEX 
			SET 
			CantEntrada = CantEntrada + (SELECT Cantidad FROM INSERTED), 
			NewCantidad = NewCantidad + (SELECT Cantidad FROM INSERTED)
		end
	   
	   ELSE
	   begin
			INSERT INTO KARDEX( 
		    IdProducto, IdDetGuiaEntrada,IdDetPecosa, 
		    Documento, Fecha, Costo, 
		    CantEntrada,CantSalida, NewCantidad)
		   
			SELECT 
			guia.IdProducto, guia.ID, NULL,
			G.NroDoc,  GETDATE(), guia.ValorUnitario,
			guia.Cantidad, 0, P.Stock + guia.Cantidad
			FROM PRODUCTO P INNER JOIN INSERTED guia  ON guia.IdProducto = P.ID
			INNER JOIN GUIA_ENTRADA G on guia.IdGuiaEntrada = G.ID
		   
	   end
	   
	   Update P set P.STOCK = P.STOCK + D.Cantidad
		   from PRODUCTO as P inner join
		   INSERTED as D on D.IdProducto = P.Id
/******************/

/******************/
ALTER trigger [dbo].[ActualizarGuiaEntrada]
   on [dbo].[DETALLE_GUIA_ENTRADA]
   for Update
   as
   Update P set P.Stock = P.Stock - D.Cantidad
   from PRODUCTO as P inner join
   DELETED as D on D.IdProducto = P.Id
  
   DELETE FROM KARDEX
   where IdDetGuiaEntrada = (SELECT DG.ID FROM DELETED DG)
   and IdProducto = (SELECT DG.IdProducto FROM DELETED DG)
   and CantEntrada = (SELECT DG.Cantidad FROM DELETED DG)
   and Fecha = (SELECT G.Fecha FROM DELETED DG
   INNER JOIN GUIA_ENTRADA G ON DG.IdGuiaEntrada = G.ID)
   
   INSERT INTO KARDEX( IdProducto, IdDetGuiaEntrada,IdDetPecosa, 
   Fecha, Costo, 
   CantEntrada,CantSalida, NewCantidad)
   
   SELECT  guia.IdProducto, guia.ID, NULL, 
   GETDATE(), guia.ValorUnitario,
   guia.Cantidad, 0, P.Stock + guia.Cantidad
   FROM PRODUCTO P INNER JOIN INSERTED guia  
   ON guia.IdProducto = P.ID
   
   Update P set P.Stock = P.Stock + I.Cantidad
   from PRODUCTO as P inner join
   INSERTED as I on I.IdProducto = P.Id
/******************/


/******************/
ALTER trigger [dbo].[EliminarGuiaEntrada]
   on [dbo].[DETALLE_GUIA_ENTRADA]
   for Delete
   as
   Update P set P.Stock = P.Stock - D.Cantidad
   from PRODUCTO as P inner join
   DELETED as D on D.IdProducto = P.Id
   
   DELETE FROM KARDEX
   where IdDetGuiaEntrada = (SELECT DG.ID FROM DELETED DG)
   and IdProducto = (SELECT DG.IdProducto FROM DELETED DG)
   and CantEntrada = (SELECT DG.Cantidad FROM DELETED DG)
   and Fecha = (SELECT G.Fecha FROM DELETED DG
   INNER JOIN GUIA_ENTRADA G ON DG.IdGuiaEntrada = G.ID)
/******************/


STORE PROCEDURES
/******************/
CREATE PROCEDURE [dbo].[GetGuiaEntrada]
@IdGuia int
AS
BEGIN
SELECT     P.RazonSocial, P.Ruc, P.Direccion, P.Telefono,
		   G.Fecha, G.Total, g.NroDoc,
		   ROW_NUMBER() OVER(ORDER BY DG.Id DESC) AS Item,
		   PRO.Nombre AS PRODUCTOs, UM.Descripcion UndmEDIDAS,
		   DG.Cantidad, DG.ValorUnitario,(DG.Cantidad * DG.ValorUnitario) TotalItem
		   
FROM 
UNIDAD_MEDIDA UM INNER JOIN PRODUCTO PRO ON UM.Id = PRO.IdUnidadMedida
INNER JOIN DETALLE_GUIA_ENTRADA DG ON PRO.Id = DG.IdProducto
INNER JOIN GUIA_ENTRADA G ON DG.IdGuiaEntrada = G.Id 
INNER JOIN PROVEEDOR P ON G.IdProveedor = P.Id
WHERE P.Id = @IdGuia
END
/******************/


/*****************/
create PROCEDURE [dbo].[GetPecosa]
@IdPecosa int
AS
BEGIN
SELECT     (T.Nombres + ' ' + T.ApePaterno + ' ' + T.ApeMaterno)  RazonSocial, T.Dni,
		   PEC.Fecha, PEC.Total, PEC.NroDoc,
		   ROW_NUMBER() OVER(ORDER BY Dp.Id DESC) AS Item,
		   PRO.Nombre AS PRODUCTOs, UM.Descripcion UndmEDIDAS,
		   DP.Cantidad, DP.ValorUnitario,(DP.Cantidad * DP.ValorUnitario) TotalItem
		   
FROM 
UNIDAD_MEDIDA UM INNER JOIN PRODUCTO PRO ON UM.Id = PRO.IdUnidadMedida
INNER JOIN DETALLE_PECOSA DP ON PRO.Id = DP.IdProducto
INNER JOIN PECOSA PEC ON DP.IdPecosa = PEC.Id 
INNER JOIN TRABAJADOR T ON PEC.IdSolicitante = T.Id
WHERE PEC.Id = @IdPecosa
END
/******************/




CREATE PROCEDURE [dbo].[ASIGNAR_ROL_USUARIO]
@UserName varchar(8),
@RoleName varchar(20)
AS
declare
	@UserId int,
	@RoleId int
begin				


		SELECT  @UserId =  U.UserId
		FROM dbo.UserProfile U  
		where U.UserName = @UserName
		
		SELECT  @RoleId =  R.RoleId
		FROM dbo.webpages_Roles R 
		where R.RoleName = @RoleName 
		
		IF @UserId != 1
		begin
		DELETE FROM dbo.webpages_UsersInRoles WHERE UserId = @UserId
		INSERT INTO dbo.webpages_UsersInRoles VALUES(@UserId,@RoleId)		
		end
end


/******************/
CREATE PROCEDURE [dbo].[ReportePartidas]
AS
BEGIN
SELECT
CC.Codigo CodigoCC, CC.Descripcion DescripcionCC,
FF.Codigo CodigoFF, FF.Descripcion DescripcionFF,
 p.Codigo, p.Nombre , SUM(R.Monto) MontoGastado, R.Fecha
FROM REDUCIR_PRESUPUESTO R INNER JOIN PARTIDA P ON R.IdPartida = P.Id
INNER JOIN DETALLE_PARTIDA DP ON P.Id = DP.IdPartida
INNER JOIN FUENTE_FINANCIERA FF ON DP.IdFuenteFinanciera = FF.Id
INNER JOIN CENTRO_COSTO CC ON FF.IdCentroCosto = CC.ID
GROUP BY R.IdPartida, p.Codigo, p.Nombre, CC.Codigo , CC.Descripcion,FF.Codigo, FF.Descripcion,R.Fecha
END

/******************/


CREATE trigger [dbo].[RegistrarPecosa]
   on [dbo].[DETALLE_PECOSA]
   for Insert
   as
   INSERT INTO KARDEX( 
   IdProducto, IdDetGuiaEntrada,IdDetPecosa, 
   Fecha, Costo, 
   CantEntrada,CantSalida, NewCantidad)
   
   SELECT 
   pecosa.IdProducto, NULL, pecosa.ID, 
   GETDATE(), pecosa.ValorUnitario,
   0, pecosa.Cantidad, P.Stock - pecosa.Cantidad
   FROM PRODUCTO P INNER JOIN INSERTED pecosa  
   ON pecosa.IdProducto = P.ID
   
   Update P set P.STOCK = P.STOCK - D.Cantidad
   from PRODUCTO as P inner join
   INSERTED as D on D.IdProducto = P.Id



CREATE trigger [dbo].[ActualizarPecosa]
   on [dbo].[DETALLE_PECOSA]
   for Update
   as
   Update P set P.Stock =P.Stock + D.Cantidad
   from PRODUCTO as P inner join
   DELETED as D on D.IdProducto = P.Id
   
   DELETE FROM KARDEX
   where IdDetPecosa = (SELECT DP.ID FROM DELETED DP)
   and IdProducto = (SELECT DP.IdProducto FROM DELETED DP)
   and CantSalida = (SELECT DP.Cantidad FROM DELETED DP)
   and Fecha = (SELECT P.Fecha FROM DELETED DP
   INNER JOIN PECOSA P ON DP.IdPecosa = P.ID)
     
   INSERT INTO KARDEX( IdProducto, IdDetGuiaEntrada,IdDetPecosa, 
   Fecha, Costo, 
   CantEntrada,CantSalida, NewCantidad)
   
   SELECT  pecosa.IdProducto, NULL, pecosa.ID,
   GETDATE(), pecosa.ValorUnitario,
   0 , pecosa.Cantidad, P.Stock - pecosa.Cantidad
   FROM PRODUCTO P INNER JOIN INSERTED pecosa  
   ON pecosa.IdProducto = P.ID
   
   Update P set P.Stock = P.Stock - I.Cantidad
   from PRODUCTO as P inner join
   INSERTED as I on I.IdProducto = P.Id




CREATE trigger [dbo].[EliminarPecosa]
   on [dbo].[DETALLE_PECOSA]
   for Delete
   as
   Update P set P.Stock = P.Stock + D.Cantidad
   from PRODUCTO as P inner join
   DELETED as D on D.IdProducto = P.Id
   
   DELETE FROM KARDEX
   where IdDetPecosa = (SELECT Dp.ID FROM DELETED Dp)
   and IdProducto = (SELECT Dp.IdProducto FROM DELETED Dp)
   and CantSalida = (SELECT Dp.Cantidad FROM DELETED Dp)
   and Fecha = (SELECT P.FechaSalida FROM DELETED Dp 
   INNER JOIN PECOSA P ON Dp.IdPecosa = P.ID)



CREATE trigger [dbo].[RegistrarGuiaEntrada]
   on [dbo].[DETALLE_GUIA_ENTRADA]
   for Insert
   as
      
   INSERT INTO KARDEX( 
   IdProducto, IdDetGuiaEntrada,IdDetPecosa, 
   Fecha, Costo, 
   CantEntrada,CantSalida, NewCantidad)
   
   SELECT 
   guia.IdProducto, guia.ID, NULL, 
   GETDATE(), guia.ValorUnitario,
   guia.Cantidad, 0, P.Stock + guia.Cantidad
   FROM PRODUCTO P INNER JOIN INSERTED guia  
   ON guia.IdProducto = P.ID
   
   Update P set P.STOCK = P.STOCK + D.Cantidad
   from PRODUCTO as P inner join
   INSERTED as D on D.IdProducto = P.Id





CREATE trigger [dbo].[ActualizarGuiaEntrada]
   on [dbo].[DETALLE_GUIA_ENTRADA]
   for Update
   as
   Update P set P.Stock = P.Stock - D.Cantidad
   from PRODUCTO as P inner join
   DELETED as D on D.IdProducto = P.Id
  
   DELETE FROM KARDEX
   where IdDetGuiaEntrada = (SELECT DG.ID FROM DELETED DG)
   and IdProducto = (SELECT DG.IdProducto FROM DELETED DG)
   and CantEntrada = (SELECT DG.Cantidad FROM DELETED DG)
   and Fecha = (SELECT G.Fecha FROM DELETED DG
   INNER JOIN GUIA_ENTRADA G ON DG.IdGuiaEntrada = G.ID)
   
   INSERT INTO KARDEX( IdProducto, IdDetGuiaEntrada,IdDetPecosa, 
   Fecha, Costo, 
   CantEntrada,CantSalida, NewCantidad)
   
   SELECT  guia.IdProducto, guia.ID, NULL, 
   GETDATE(), guia.ValorUnitario,
   guia.Cantidad, 0, P.Stock + guia.Cantidad
   FROM PRODUCTO P INNER JOIN INSERTED guia  
   ON guia.IdProducto = P.ID
   
   Update P set P.Stock = P.Stock + I.Cantidad
   from PRODUCTO as P inner join
   INSERTED as I on I.IdProducto = P.Id
   



CREATE trigger [dbo].[EliminarGuiaEntrada]
   on [dbo].[DETALLE_GUIA_ENTRADA]
   for Delete
   as
   Update P set P.Stock = P.Stock - D.Cantidad
   from PRODUCTO as P inner join
   DELETED as D on D.IdProducto = P.Id
   
   DELETE FROM KARDEX
   where IdDetGuiaEntrada = (SELECT DG.ID FROM DELETED DG)
   and IdProducto = (SELECT DG.IdProducto FROM DELETED DG)
   and CantEntrada = (SELECT DG.Cantidad FROM DELETED DG)
   and Fecha = (SELECT G.Fecha FROM DELETED DG
   INNER JOIN GUIA_ENTRADA G ON DG.IdGuiaEntrada = G.ID)



    
	----------------------------------
	CREATE PROCEDURE [dbo].[ASIGNAR_ROL_USUARIO]
@UserName varchar(8),
@RoleName varchar(20)
AS
declare
	@UserId int,
	@RoleId int
begin				


		SELECT  @UserId =  U.UserId
		FROM dbo.UserProfile U  
		where U.UserName = @UserName
		
		SELECT  @RoleId =  R.RoleId
		FROM dbo.webpages_Roles R 
		where R.RoleName = @RoleName 
		
		IF @UserId != 1
		begin
		DELETE FROM dbo.webpages_UsersInRoles WHERE UserId = @UserId
		INSERT INTO dbo.webpages_UsersInRoles VALUES(@UserId,@RoleId)		
		end
end




CREATE trigger [dbo].[RegistrarPecosa]
   on [dbo].[DETALLE_PECOSA]
   for Insert
   as
   INSERT INTO KARDEX( 
   IdProducto, IdDetGuiaEntrada,IdDetPecosa, 
   Fecha, Costo, 
   CantEntrada,CantSalida, NewCantidad)
   
   SELECT 
   pecosa.IdProducto, NULL, pecosa.ID, 
   GETDATE(), pecosa.ValorUnitario,
   0, pecosa.Cantidad, P.Stock - pecosa.Cantidad
   FROM PRODUCTO P INNER JOIN INSERTED pecosa  
   ON pecosa.IdProducto = P.ID
   
   Update P set P.STOCK = P.STOCK - D.Cantidad
   from PRODUCTO as P inner join
   INSERTED as D on D.IdProducto = P.Id



CREATE trigger [dbo].[ActualizarPecosa]
   on [dbo].[DETALLE_PECOSA]
   for Update
   as
   Update P set P.Stock =P.Stock + D.Cantidad
   from PRODUCTO as P inner join
   DELETED as D on D.IdProducto = P.Id
   
   DELETE FROM KARDEX
   where IdDetPecosa = (SELECT DP.ID FROM DELETED DP)
   and IdProducto = (SELECT DP.IdProducto FROM DELETED DP)
   and CantSalida = (SELECT DP.Cantidad FROM DELETED DP)
   and Fecha = (SELECT P.Fecha FROM DELETED DP
   INNER JOIN PECOSA P ON DP.IdPecosa = P.ID)
     
   INSERT INTO KARDEX( IdProducto, IdDetGuiaEntrada,IdDetPecosa, 
   Fecha, Costo, 
   CantEntrada,CantSalida, NewCantidad)
   
   SELECT  pecosa.IdProducto, NULL, pecosa.ID,
   GETDATE(), pecosa.ValorUnitario,
   0 , pecosa.Cantidad, P.Stock - pecosa.Cantidad
   FROM PRODUCTO P INNER JOIN INSERTED pecosa  
   ON pecosa.IdProducto = P.ID
   
   Update P set P.Stock = P.Stock - I.Cantidad
   from PRODUCTO as P inner join
   INSERTED as I on I.IdProducto = P.Id




CREATE trigger [dbo].[EliminarPecosa]
   on [dbo].[DETALLE_PECOSA]
   for Delete
   as
   Update P set P.Stock = P.Stock + D.Cantidad
   from PRODUCTO as P inner join
   DELETED as D on D.IdProducto = P.Id
   
   DELETE FROM KARDEX
   where IdDetPecosa = (SELECT Dp.ID FROM DELETED Dp)
   and IdProducto = (SELECT Dp.IdProducto FROM DELETED Dp)
   and CantSalida = (SELECT Dp.Cantidad FROM DELETED Dp)
   and Fecha = (SELECT P.FechaSalida FROM DELETED Dp 
   INNER JOIN PECOSA P ON Dp.IdPecosa = P.ID)



CREATE trigger [dbo].[RegistrarGuiaEntrada]
   on [dbo].[DETALLE_GUIA_ENTRADA]
   for Insert
   as
      
   INSERT INTO KARDEX( 
   IdProducto, IdDetGuiaEntrada,IdDetPecosa, 
   Fecha, Costo, 
   CantEntrada,CantSalida, NewCantidad)
   
   SELECT 
   guia.IdProducto, guia.ID, NULL, 
   GETDATE(), guia.ValorUnitario,
   guia.Cantidad, 0, P.Stock + guia.Cantidad
   FROM PRODUCTO P INNER JOIN INSERTED guia  
   ON guia.IdProducto = P.ID
   
   Update P set P.STOCK = P.STOCK + D.Cantidad
   from PRODUCTO as P inner join
   INSERTED as D on D.IdProducto = P.Id





CREATE trigger [dbo].[ActualizarGuiaEntrada]
   on [dbo].[DETALLE_GUIA_ENTRADA]
   for Update
   as
   Update P set P.Stock = P.Stock - D.Cantidad
   from PRODUCTO as P inner join
   DELETED as D on D.IdProducto = P.Id
  
   DELETE FROM KARDEX
   where IdDetGuiaEntrada = (SELECT DG.ID FROM DELETED DG)
   and IdProducto = (SELECT DG.IdProducto FROM DELETED DG)
   and CantEntrada = (SELECT DG.Cantidad FROM DELETED DG)
   and Fecha = (SELECT G.Fecha FROM DELETED DG
   INNER JOIN GUIA_ENTRADA G ON DG.IdGuiaEntrada = G.ID)
   
   INSERT INTO KARDEX( IdProducto, IdDetGuiaEntrada,IdDetPecosa, 
   Fecha, Costo, 
   CantEntrada,CantSalida, NewCantidad)
   
   SELECT  guia.IdProducto, guia.ID, NULL, 
   GETDATE(), guia.ValorUnitario,
   guia.Cantidad, 0, P.Stock + guia.Cantidad
   FROM PRODUCTO P INNER JOIN INSERTED guia  
   ON guia.IdProducto = P.ID
   
   Update P set P.Stock = P.Stock + I.Cantidad
   from PRODUCTO as P inner join
   INSERTED as I on I.IdProducto = P.Id
   



CREATE trigger [dbo].[EliminarGuiaEntrada]
   on [dbo].[DETALLE_GUIA_ENTRADA]
   for Delete
   as
   Update P set P.Stock = P.Stock - D.Cantidad
   from PRODUCTO as P inner join
   DELETED as D on D.IdProducto = P.Id
   
   DELETE FROM KARDEX
   where IdDetGuiaEntrada = (SELECT DG.ID FROM DELETED DG)
   and IdProducto = (SELECT DG.IdProducto FROM DELETED DG)
   and CantEntrada = (SELECT DG.Cantidad FROM DELETED DG)
   and Fecha = (SELECT G.Fecha FROM DELETED DG
   INNER JOIN GUIA_ENTRADA G ON DG.IdGuiaEntrada = G.ID)